import pandas as pd
import sqlite3
import os
import re

def convert_university_excel(excel_file, table_name='universities', db_name='universities.db'):
    """
    将大学列表Excel文件转换为结构化的SQL脚本
    
    参数:
    excel_file: Excel文件路径
    table_name: 数据库表名
    db_name: SQLite数据库文件名
    """
    try:
        # 读取Excel文件
        df = pd.read_excel(excel_file, header=None)
        print(f"成功读取Excel文件，共有 {len(df)} 行数据")
    except Exception as e:
        print(f"读取Excel文件失败: {e}")
        return None
    
    # 创建新的DataFrame来存储结构化数据
    structured_data = []
    
    current_region = ""
    university_count = 0
    
    # 遍历每一行数据
    for index, row in df.iterrows():
        # 检查是否是地区行（如"北京市（23所）"）
        first_col = str(row[0]) if pd.notna(row[0]) else ""
        
        if re.match(r'.*市（\d+所）$', first_col) or re.match(r'.*省（\d+所）$', first_col) or re.match(r'.*自治区（\d+所）$', first_col):
            current_region = first_col.split("（")[0]
            continue
            
        # 检查是否是表头行
        if first_col == "序号" and pd.notna(row[1]) and str(row[1]) == "学校名称":
            continue
            
        # 检查是否是标题行
        if "全国成人高等学校名单" in first_col:
            continue
            
        # 处理大学数据行
        if pd.notna(row[1]):  # 学校名称不为空
            university_count += 1
            university_data = {
                'id': university_count,
                'region': current_region,
                'serial_number': row[0] if pd.notna(row[0]) else None,
                'name': row[1] if pd.notna(row[1]) else "",
                'code': row[2] if pd.notna(row[2]) else "",
                'department': row[3] if pd.notna(row[3]) else "",
                'remark': row[4] if pd.notna(row[4]) else ""
            }
            structured_data.append(university_data)
    
    # 创建结构化的DataFrame
    structured_df = pd.DataFrame(structured_data)
    print(f"共处理 {len(structured_data)} 所大学的数据")
    
    # 连接到SQLite数据库
    conn = sqlite3.connect(db_name)
    
    # 将DataFrame写入数据库
    structured_df.to_sql(table_name, conn, if_exists='replace', index=False)
    
    # 生成CREATE TABLE语句
    create_table_sql = f"""CREATE TABLE {table_name} (
    id INTEGER PRIMARY KEY,
    region TEXT,
    serial_number INTEGER,
    name TEXT,
    code TEXT,
    department TEXT,
    remark TEXT
);\n\n"""
    
    # 生成INSERT语句
    insert_sql = ""
    for _, row in structured_df.iterrows():
        insert_sql += f"INSERT INTO {table_name} (id, region, serial_number, name, code, department, remark) VALUES ("
        insert_sql += f"{row['id']}, "
        insert_sql += f"'{row['region']}', " if pd.notna(row['region']) else "NULL, "
        insert_sql += f"{row['serial_number']}, " if pd.notna(row['serial_number']) else "NULL, "
        insert_sql += f"'{str(row['name']).replace("'", "''")}', " if pd.notna(row['name']) else "NULL, "
        insert_sql += f"'{row['code']}', " if pd.notna(row['code']) else "NULL, "
        insert_sql += f"'{str(row['department']).replace("'", "''")}', " if pd.notna(row['department']) else "NULL, "
        insert_sql += f"'{str(row['remark']).replace("'", "''")}'" if pd.notna(row['remark']) else "NULL"
        insert_sql += ");\n"
    
    # 写入SQL文件
    sql_file = f"{os.path.splitext(excel_file)[0]}_structured.sql"
    with open(sql_file, 'w', encoding='utf-8') as f:
        f.write(create_table_sql)
        f.write(insert_sql)
    
    conn.close()
    
    print(f"结构化SQL脚本已生成: {sql_file}")
    print(f"SQLite数据库已创建: {db_name}")
    return sql_file

def generate_mysql_script(excel_file, table_name='universities'):
    """
    生成MySQL脚本
    
    参数:
    excel_file: Excel文件路径
    table_name: 数据库表名
    """
    try:
        # 读取Excel文件
        df = pd.read_excel(excel_file, header=None)
    except Exception as e:
        print(f"读取Excel文件失败: {e}")
        return None
    
    # 创建新的DataFrame来存储结构化数据
    structured_data = []
    
    current_region = ""
    university_count = 0
    
    # 遍历每一行数据
    for index, row in df.iterrows():
        # 检查是否是地区行（如"北京市（23所）"）
        first_col = str(row[0]) if pd.notna(row[0]) else ""
        
        if re.match(r'.*市（\d+所）$', first_col) or re.match(r'.*省（\d+所）$', first_col) or re.match(r'.*自治区（\d+所）$', first_col):
            current_region = first_col.split("（")[0]
            continue
            
        # 检查是否是表头行
        if first_col == "序号" and pd.notna(row[1]) and str(row[1]) == "学校名称":
            continue
            
        # 检查是否是标题行
        if "全国成人高等学校名单" in first_col:
            continue
            
        # 处理大学数据行
        if pd.notna(row[1]):  # 学校名称不为空
            university_count += 1
            university_data = {
                'id': university_count,
                'region': current_region,
                'serial_number': row[0] if pd.notna(row[0]) else None,
                'name': row[1] if pd.notna(row[1]) else "",
                'code': row[2] if pd.notna(row[2]) else "",
                'department': row[3] if pd.notna(row[3]) else "",
                'remark': row[4] if pd.notna(row[4]) else ""
            }
            structured_data.append(university_data)
    
    # 创建结构化的DataFrame
    structured_df = pd.DataFrame(structured_data)
    
    # 生成CREATE TABLE语句
    create_table_sql = f"""CREATE TABLE `{table_name}` (
  `id` int(11) NOT NULL,
  `region` varchar(50) DEFAULT NULL,
  `serial_number` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `code` varchar(20) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `remark` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\n\n"""
    
    # 生成INSERT语句
    insert_sql = ""
    for _, row in structured_df.iterrows():
        insert_sql += f"INSERT INTO `{table_name}` (`id`, `region`, `serial_number`, `name`, `code`, `department`, `remark`) VALUES ("
        insert_sql += f"{row['id']}, "
        insert_sql += f"'{row['region']}', " if pd.notna(row['region']) else "NULL, "
        insert_sql += f"{row['serial_number']}, " if pd.notna(row['serial_number']) else "NULL, "
        insert_sql += f"'{str(row['name']).replace("'", "\\'")}', " if pd.notna(row['name']) else "NULL, "
        insert_sql += f"'{row['code']}', " if pd.notna(row['code']) else "NULL, "
        insert_sql += f"'{str(row['department']).replace("'", "\\'")}', " if pd.notna(row['department']) else "NULL, "
        insert_sql += f"'{str(row['remark']).replace("'", "\\'")}'" if pd.notna(row['remark']) else "NULL"
        insert_sql += ");\n"
    
    # 写入SQL文件
    sql_file = f"{os.path.splitext(excel_file)[0]}_mysql_structured.sql"
    with open(sql_file, 'w', encoding='utf-8') as f:
        f.write("SET NAMES utf8mb4;\n")
        f.write("SET FOREIGN_KEY_CHECKS = 0;\n\n")
        f.write(create_table_sql)
        f.write(insert_sql)
        f.write("\nSET FOREIGN_KEY_CHECKS = 1;\n")
    
    print(f"MySQL结构化脚本已生成: {sql_file}")
    return sql_file

if __name__ == "__main__":
    # 使用示例
    excel_file = "../W020250627301230143042.xls"
    
    # 转换为结构化的SQLite脚本
    try:
        convert_university_excel(excel_file, "universities", "universities.db")
    except Exception as e:
        print(f"转换为结构化SQLite时出错: {e}")
    
    # 生成结构化的MySQL脚本
    try:
        generate_mysql_script(excel_file, "universities")
    except Exception as e:
        print(f"生成结构化MySQL脚本时出错: {e}")